<?php
class HelpDesk
{
	const RETURN_TREE = 1;
	const RETURN_NO_TREE = 0;
	private static $_instance = null;
	private $_db = null;
	public static $_arrTicketStatus = array('0'=>'Closed', '1'=>'Open');
	public static $_arrTicketRate = array('0'=>'Not rated', '1'=>'Bad', '2'=>'Poor', '3'=>'Regular', '4'=>'Good', '5'=>'Excellent');

	
	private function __construct()
	{
		$this->_db = Yii::app()->db;
		$this->_db->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER);
	}
	
	public static function getInstance() 
    {
        if (null === self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }
	
	public function getModerators($cateId){
		try{
			if(!is_array($cateId)) $cateId = explode(',', $cateId);
			$arrUsers = array();
			foreach($cateId as $R){
				$settings = $this->_db->createCommand()->select('setting_code')->from('tbl_settings')->where('setting_code LIKE :code  AND setting_group=\'helpdesk\'', array(':code'=>'mod_'.$R.'_%'))->queryAll();
				if(!empty($settings)){
					foreach($settings as $R){
						$pieces = explode('_', $R['setting_code']);
						if(!empty($pieces[2])) $arrUsers[] = Users::getInstance()->get($pieces[2]);	
					}
				}
			}
			return $arrUsers;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
	public function getModeratorCategories($userId){
		try{
			$settings = Yii::app()->db->createCommand()->select('setting_code')->from('tbl_settings')->where('setting_code LIKE :code  AND setting_group=\'helpdesk\'', array(':code'=>'mod_%_'.$userId))->queryAll();
			$cateIds = array();
			foreach($settings as $R){
				$pieces = explode('_', $R['setting_code']);
				if(!empty($pieces[1])) $cateIds[] = $pieces[1];	
			}
			return $cateIds;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateModeratorCategories($userId, $cateId){
		try{
			if(!is_array($cateId)) $cateId = explode(',', $cateId);
			//remove moderators from old cats
			$this->removeModerator($userId);
			foreach($cateId as $R){
				$code = 'mod_'.$R.'_'.$userId;
				$vale = date('d-m-Y H:i:s');
				//insert new
				Yii::app()->db->createCommand()->insert('tbl_settings', array('setting_code' => $code, 'setting_value' => 1, 'setting_group' => 'helpdesk'));
			}
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function removeModerator($userId){
		try{
			//delete old cats
			Yii::app()->db->createCommand()->delete('tbl_settings', 'setting_code LIKE :code AND setting_group=\'helpdesk\'', array(':code'=>'mod_%_'.$userId));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function removeCategory($cateId){
		try{
			if(!is_array($cateId)) $cateId = explode(',', $cateId);
			//delete old cats
			foreach($cateId as $R){
				Yii::app()->db->createCommand()->delete('tbl_settings', 'setting_code LIKE :code AND setting_group=\'helpdesk\'', array(':code'=>'mod_'.$R.'_%'));
			}
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getDepts($includeSubjects = false)
	{
		try{
			$dept = Categories::getInstance()->get('helpdesk-departments');
			if(empty($dept)) die('Function has not been installed yet!');	
			return $arrDepts = Categories::getInstance()->getByParent(array(
				'category_parent' => $dept['category_id'],
				'category_status' => 1,
				'recursive_level' => empty($includeSubjects)?0:1,
			));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getSubjects($deptId)
	{
		try{
			return $arrSubjects = Categories::getInstance()->getByParent(array(
				'category_parent' => $deptId,
				'category_status' => 1,
				'recursive_level' => 0
			));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	//DuyAnh :: analyse category id is subject or department
	public function analyseTicketCategory($cateIds, $arrCategories = array())
	{
		if(empty($arrDepts)){
			$arrDepts = $this->getDepts(true);	
		}
		//analyse
		$ticketDepartment = null;
		$ticketSubject = null;
		if(!empty($cateIds)){
			$arrCateIds = !is_array($cateIds)?explode(',', $cateIds):$cateIds;		
			foreach($arrCategories as $D){
				if(in_array($D['category_id'], $arrCateIds)){
					if($D['category_level'] == 0) $ticketDepartment = $D;
					else if($D['category_level'] == 1) $ticketSubject = $D;
				}
			}
		}
		return array('department' => $ticketDepartment, 'subject' => $ticketSubject);
	}
	
	public function getDocumentFolders()
	{
		try{
			$folder = Categories::getInstance()->get('helpdesk-documents');
			if(empty($folder)) die('Function has not been installed yet!');	
			return $arrFolders = Categories::getInstance()->getByParent(array(
				'category_parent' => $folder['category_id'],
				'category_status' => 1,
				'recursive_level' => 3
			));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getFAQFolders()
	{
		try{
			$folder = Categories::getInstance()->get('helpdesk-faqs');
			if(empty($folder)) die('Function has not been installed yet!');	
			return $arrFolders = Categories::getInstance()->getByParent(array(
				'category_parent' => $folder['category_id'],
				'category_status' => 1,
				'recursive_level' => 3
			));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function sendMail($case, array $args){
		try{
			//mail settings
			$settings  = Settings::getInstance()->getList(array('setting_group'=>'mail'), RETURN_KEY_VALUE_ARRAY);

			switch($case){
				case 'ticket':
				{
					$defaults = array(
						'creator_id' => null,
						'ticket_creator_id' => null,						
						'ticket_id' => null,
						'ticket_title' => null,
						'ticket_body' => null,
						'category_id' => null
					);
					$args = array_merge($defaults, $args);
					
					if(empty($args['creator_id'])) throw new Exception('Mailer Error: Can not send mail. User is invalid');
					
					//creator (có thể là người tạo ticket & là người reply)
					$creator = Users::getInstance()->get($args['creator_id']);
					
					//email template
					$template = EmailTemplates::getInstance()->getByCode('helpdesk-ticket');
					
					//moderator
					$moderators = $this->getModerators($args['category_id']);
					
					//prepare email content
					$ticketDepartment = array();
					$ticketSubject = array();
					if(!empty($args['category_id'])){
						//lấy thông tin dept và subject (nếu có)
						$arrPostCateIds = $args['category_id'];
						if(!is_array($args['category_id'])) $arrPostCateIds = explode(',', $arrPostCateIds);
						$arrDepts = HelpDesk::getInstance()->getDepts(true);
						foreach($arrDepts as $D){
							if(in_array($D['category_id'], $arrPostCateIds)){
								if($D['category_level'] == 0) $ticketDepartment = $D;
								else if($D['category_level'] == 1) $ticketSubject = $D;
							}
						}
					}

					$arrHoles = array(	'#ticket-creator',
										'#ticket-title#', 
										'#ticket-body#', 
										'#ticket-id#', 
										'#department#', 
										'#subject#', 
										'#ticket-url#');
					$arrReplacements = array(
										$creator['user_fullname'].' &lt;'.$creator['user_email'].'&gt;',
										$args['ticket_title'],
										$args['ticket_body'],
										'#'.$args['ticket_id'],
										isset($ticketDepartment['category_name'])?$ticketDepartment['category_name']:'-',
										isset($ticketSubject['category_name'])?$ticketSubject['category_name']:'-',
										BASE_URL.'/tickets/'.$args['ticket_id'].'.html'
									);
					$strContent = str_replace($arrHoles, $arrReplacements, $template['template_body']);
					$strSubject = str_replace($arrHoles, $arrReplacements, $template['template_subject']);

					//determine sending flows
					if($args['creator_id'] == $args['ticket_creator_id']){ //tạo mới
						//tạo mới: gửi cho người phụ trách
						$receivers = $moderators;
					}
					else{ //reply
						//trả lời: gửi cho người tạo và những người phụ trách khác
						$ticketCreator = Users::getInstance()->get($args['ticket_creator_id']);
						$receivers = array($ticketCreator);
						$receivers = array_merge($receivers, $moderators);					
					}
					//lọc danh sách nhận, đảm bảo chỉ nhận mail 1 lần (vì có thể 1 người phụ trách nhiều subject)
					$temp = array();
					foreach($receivers as $R){
						//kiểm tra nếu vừa tạo mới vừa phụ trách thì ko gửi mail
						if($R['user_id'] == $args['creator_id']) continue;
						//kiểm tra xem có muốn nhận email không?
						$profile = Users::getInstance()->getProfile($R['user_id']);
						if(empty($profile['profile_allow_notify'])) continue; //không đồng ý -> bỏ qua
						//add vào danh sách gửi
						$temp[$R['user_id']] = array(
							'user_email' => $R['user_email'],
							'user_fullname' => $R['user_fullname']
						);
					}
					$receivers = $temp;
					foreach($receivers as $R){
						//prepare content						
						$params = array(
							'mailer' => $settings,
							'from' => 'no-reply@datvietvac.vn',
							'fromName' => 'HelpDesk System',
							'to' => $R['user_email'],
							'toName' => $R['user_fullname'],
							'replyTo' => '',
							'replyToName' => '',
							'subject' => $strSubject,
							'body' => $strContent,
							'attachment' => ''
						);
						//send mail
						$this::mail($params);
					}
					break;	
				} //end case ticket
				case 'rate':
				{
					$defaults = array(
						'ticket_id' => null,
						'reply_id' => null,
						'rated_by' => null,						
						'score' => '0',
						'ticketInfo' => null,
						'replyInfo' => null,
					);
					$args = array_merge($defaults, $args);
					
					if(empty($args['ticket_id']) || empty($args['reply_id']) || empty($args['ticketInfo']) || empty($args['replyInfo'])) throw new Exception('Mailer Error: Can not send mail because invalid data');
					
					//get post
					$post = $args['replyInfo'];
					
					//get parent post
					$parentPost = $args['ticketInfo'];
					
					//replier
					$replier = Users::getInstance()->get($post['post_creator_id']);	
					
					//kiểm tra xem replier có muốn nhận email ko?
					$replierProfile = Users::getInstance()->getProfile($replier['user_id']);
					if(empty($$replierProfile['profile_allow_notify'])) return; //không đồng ý -> kết thúc
					
					//ticket creator
					$creator = Users::getInstance()->get($args['rated_by']);											
					
					//email template
					$template = EmailTemplates::getInstance()->getByCode('helpdesk-ticket');
					
					//prepare category
					$ticketDepartment = array();
					$ticketSubject = array();
					if(!empty($parentPost['post_category_id'])){
						$arrPostCateIds = $parentPost['post_category_id'];
						if(!is_array($parentPost['post_category_id'])) $arrPostCateIds = explode(',', $arrPostCateIds);
						$arrDepts = HelpDesk::getInstance()->getDepts(true);
						foreach($arrDepts as $D){
							if(in_array($D['category_id'], $arrPostCateIds)){
								if($D['category_level'] == 0) $ticketDepartment = $D;
								else if($D['category_level'] == 1) $ticketSubject = $D;
							}
						}
					}

					//prepare email content
					$arrHoles = array(	'#ticket-creator',
										'#ticket-title#', 
										'#ticket-body#', 
										'#ticket-id#', 
										'#department#', 
										'#subject#', 
										'#ticket-url#');
					$arrReplacements = array(						
										$creator['user_fullname'].' &lt;'.$creator['user_email'].'&gt;',
										$parentPost['post_title'],
										$creator['user_fullname'].' '.Yii::t('helpdesk','rated your reply').': <b>'.Yii::t('helpdesk',HelpDesk::$_arrTicketRate[$args['score']])."</b> (".Yii::t('helpdesk', 'score: {n}/5', intval($args['score'])).")"."\n<br><br>----------------------------------------------<br><br>\n".$post['post_body'],
										'#'.$parentPost['post_id'],
										isset($ticketDepartment['category_name'])?$ticketDepartment['category_name']:'-',
										isset($ticketSubject['category_name'])?$ticketSubject['category_name']:'-',
										BASE_URL.'/tickets/'.$args['ticket_id'].'.html'
									);
					$strContent = str_replace($arrHoles, $arrReplacements, $template['template_body']);
					$strSubject = str_replace($arrHoles, $arrReplacements, $template['template_subject']);
					
					$this::mail(array(
						'mailer' => $settings,
						'from' => 'no-reply@datvietvac.vn',
						'fromName' => 'HelpDesk System',
						'to' => $replier['user_email'],
						'toName' => $replier['user_fullname'],
						'replyTo' => '',
						'replyToName' => '',
						'subject' => $strSubject,
						'body' => $strContent,
						'attachment' => ''
					));
					break;	
				}
				case 'forget-password': 
				{
					$defaults = array(
						'token' => null,
						'token_url' => null,
						'user' => null,						
					);
					$args = array_merge($defaults, $args);
					
					$user = $args['user'];
					
					//email template
					$template = EmailTemplates::getInstance()->getByCode('helpdesk-email-template');
					
					//prepare email content
					$arrHoles = array(	'#title#', 
										'#body#' );
					$arrReplacements = array(			
						Yii::t('helpdesk', 'Request change password'),
						nl2br(Yii::t('helpdesk', "You or someone requested for new password. If you really want to update your password, please click on the following url: \n\n {token_url} \n\n This link will automaticaly expired after 24 hours. \n\n HelpDesk System", array('{token_url}' => "<a href=\"".$args['token_url']."\" target=\"_blank\">".$args['token_url']."</a>")))
					);
					$strContent = str_replace($arrHoles, $arrReplacements, $template['template_body']);
					$strSubject = str_replace('#subject#', Yii::t('helpdesk', 'Request change password'), $template['template_subject']);
					$this::mail(array(
						'mailer' => $settings,
						'from' => 'no-reply@datvietvac.vn',
						'fromName' => 'HelpDesk System',
						'to' => $user['user_email'],
						'toName' => $user['user_fullname'],
						'replyTo' => '',
						'replyToName' => '',
						'subject' => $strSubject,
						'body' => $strContent,
						'attachment' => ''
					));
					break;	
				}
				case 'forward-ticket': 
				{
					//email template
					$template = EmailTemplates::getInstance()->getByCode('helpdesk-email-template');
					
					//prepare email content
					$arrHoles = array(	'#title#', 
										'#body#' );
					$arrReplacements = array(			
						Yii::t('helpdesk', 'Forward ticket'),
						nl2br($args['message'])
					);
					$strContent = str_replace($arrHoles, $arrReplacements, $template['template_body']);
					$strSubject = str_replace('#subject#', Yii::t('helpdesk', 'Forward Ticket'), $template['template_subject']);
					
					//determine sending flows
					$receivers = array();
					if($args['forwarder'] != $args['ticket_creator_id']){ 
						//Nếu forwarder không phải là người tạo ticket => gửi
						$receivers[] = Users::getInstance()->get($args['ticket_creator_id']);
					}
					//gửi cho người phụ trách cate mới
					$new_moderators = $this->getModerators($args['new_categories']);
					//gửi cho người phụ trách cate cũ
					$old_moderators = $this->getModerators($args['old_categories']);
					$receivers = array_merge($receivers, $new_moderators, $old_moderators);					
					//lọc danh sách nhận, đảm bảo chỉ nhận mail 1 lần (vì có thể 1 người phụ trách nhiều subject)
					$temp = array();
					foreach($receivers as $R){
						//kiểm tra nếu vừa forward vừa phụ trách thì ko gửi mail
						if($R['user_id'] == $args['forwarder']) continue;
						//kiểm tra xem có trong danh sách chưa?
						if(!isset($temp[$R['user_id']])){
							//kiểm tra xem có muốn nhận email không?
							$profile = Users::getInstance()->getProfile($R['user_id']);
							if(empty($profile['profile_allow_notify'])) continue; //không đồng ý -> bỏ qua
							//add vào danh sách gửi
							$temp[$R['user_id']] = array(
								'user_email' => $R['user_email'],
								'user_fullname' => $R['user_fullname']
							);
						}
					}
					$receivers = $temp;
					//o($receivers);
					//send
					foreach($receivers as $R){
						$this::mail(array(
							'mailer' => $settings,
							'from' => 'no-reply@datvietvac.vn',
							'fromName' => 'HelpDesk System',
							'to' => $R['user_email'],
							'toName' => $R['user_fullname'],
							'replyTo' => '',
							'replyToName' => '',
							'subject' => $strSubject,
							'body' => $strContent,
							'attachment' => ''
						));
					}
					break;
				}
			}
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
		
	public static function mail(array $args)
	{
		try{
			$defaults = array(
				'mailer' => array(),
				'from' => '',
				'fromName' => '',
				'to' => '',
				'toName' => '',
				'replyTo' => '',
				'replyToName' => '',
				'subject' => '',
				'body' => '',
				'attachment' => ''	
			);
			$args = array_merge($defaults, $args);
			
			//include mailer lib
			require_once 'PHPMailer/PHPMailerAutoload.php';
			
			//Create a new PHPMailer instance
			$mail = new PHPMailer();
			
			//Setup mailer method
			if($args['mailer']['mailer'] == 'smtp'){
				$mail->IsSMTP();
				$mail->Host = $args['mailer']['smtp_host'];
				$mail->Port = $args['mailer']['smtp_port'];				
				$mail->SMTPAuth = $args['mailer']['smtp_authentication'];;
				$mail->Username = $args['mailer']['smtp_user'];
				$mail->Password = $args['mailer']['smtp_password'];
			} else {
				$mail->IsMail();
				/*if not set, use default value in php.ini*/
				if($args['mailer']['sendmail_path'] != '')
					$mail->Sendmail = $args['mailer']['sendmail_path'];	
			}
			
			//set charset
			$mail->CharSet="utf-8";
			
			//Setup email content
			$mail->setFrom($args['from']);
			$mail->addAddress($args['to']);
			$mail->addReplyTo($args['replyTo']);
			$mail->Subject = $args['subject'];
			$mail->msgHTML($args['body']);
			$mail->AltBody = strip_tags($args['body']);
			$mail->addAttachment($args['attachment']);
			
			//send email
			if (!$mail->send()) {
				throw new Exception("Mailer Error: " . $mail->ErrorInfo);
			} else {
				return true;
			}
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}			
	}
	
	public function getLogs(array $args = array(), $intPage = 1, $intLimit = MIN_ROWS_PER_PAGE){
		try{
			return Logs::getInstance()->getList($args, $intPage, $intLimit);
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}		
	}
	
	public function getDeptStatistic(){
		try{
			$strSql = " SELECT c.category_id, c.category_name, COUNT(pc.post_id) as total, 
							   COUNT(CASE WHEN p.post_status = 1 THEN 1 ELSE null END) AS `open`,
							   COUNT(CASE WHEN p.post_status = 0 THEN 1 ELSE null END) AS `closed`
						FROM (
							SELECT category_id, category_name
							FROM tbl_cms_categories
							WHERE category_parent = (
								SELECT category_id
								FROM tbl_cms_categories
								WHERE category_code = 'helpdesk-departments'
							) AND category_status = 1
						) c LEFT JOIN tbl_cms_post_category pc ON c.category_id = pc.category_id INNER JOIN tbl_cms_posts p ON p.post_id = pc.post_id
						GROUP BY c.category_name; ";
			$cmd = $this->_db->createCommand($strSql);
			return $arrResults = $cmd->queryAll();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}		
	}
	
	public function getSubjectStatistic(){
		try{
			$strSql = " SELECT c.*, count(pc.post_id) as total
						FROM (
							SELECT c1.category_name as department_name, c2.category_name as subject_name, c1.category_id as department_id, c2.category_id as subject_id
							FROM tbl_cms_categories c
							LEFT JOIN tbl_cms_categories c1 ON c1.category_parent = c.category_id
							LEFT JOIN tbl_cms_categories c2 ON c2.category_parent = c1.category_id
							WHERE c.category_code = 'helpdesk-departments'
							ORDER BY c.category_name, c1.category_name, c2.category_name
						) c LEFT JOIN tbl_cms_post_category pc ON c.subject_id = pc.category_id
						GROUP BY c.subject_id;";
			$cmd = $this->_db->createCommand($strSql);
			return $arrResults = $cmd->queryAll();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
	
}